import json
import pymysql as mysql
import requests as rq

sql_conn = mysql.connect(host="127.0.0.1", user="root", password="111111", database="movies")
cursor = sql_conn.cursor()

name_list = []
published_at_list = []
score_list = []
minute_list = []
category_list = []
for i in range(0,10):
    body = rq.get("https://spa1.scrape.center/api/movie?limit=10&offset={}".format(i*10),verify=False)
    json_body = json.loads(body.text)
    results = json_body.get("results")
    for result in results:
        name = result.get("name")
        published_at = result.get("published_at")
        score = result.get("score")
        minute = result.get("minute")
        categories = result.get("categories")
        print(name,published_at,score,minute,categories)
        name_list.append(name)
        published_at_list.append(published_at)
        score_list.append(score)
        category_list.append(str(categories))
        minute_list.append(minute)

# 插入到数据库
for name, published_at, score, categories, minute in zip(name_list, published_at_list, score_list, category_list,
                                                               minute_list):
    sql = "insert into school(name,published_at,score,categories,minute) values(\"{}\",\"{}\",\"{}\",\"{}\",\"{}\")".format(
        name, published_at,
        score, categories,
        minute)
    insertLength = cursor.execute(sql)
    print(insertLength)

sql_conn.commit()  # 提交
sql_conn.close()
